﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace TheBullysStore.Models.ModuloVentas.Consulta
{
    public class ConsultaDAO
    {
        //
        // GET: /ConsultaDAO/

        public string correoVendedor(string id_propiedad) {
            ConsultaBean resultado=new ConsultaBean();
            try
            {

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT id_vendedor FROM propiedad_en_venta WHERE id_propiedad="+id_propiedad;
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
               
                while (dataReader.Read())
                {
                    resultado.Descripcion = Convert.ToString(dataReader["id_vendedor"]);
                }
                sqlCon.Close();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("llegue aqui2.5 " + e.ToString());
                return null;
            }
            try
            {

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT Correo FROM persona WHERE id_persona=" + resultado.Descripcion;
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    resultado.correo = Convert.ToString(dataReader["id_vendedor"]);
                }
                sqlCon.Close();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("llegue aqui2.6 " + e.ToString());
                return null;
            }

            return resultado.correo;
        }

        public string RegistrarConsulta(ConsultaBean consulta)
        {

            try
            {

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
               sqlCon.Open();
                string commandString = "INSERT INTO consulta (Id_Propiedad, Descripcion, estado, fecha, correo) VALUES ('" + consulta.Id_Propiedad + "','" + consulta.Descripcion + "','1', GETDATE(),'"+consulta.correo+"')";
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                consulta.conexion = "Bien";
            }
            catch
            {
                consulta.conexion = "Falla en Conexion";
            }
            return consulta.conexion;
        }
        public Boolean ExisteConsulta(string Nro_Documento_cliente_web)
        {
            return true;//modificar
        }

        //ACA INICIA LA PARTE QUE SE USA PARA EL BUSCAR CONSULTA

        public List<ConsultaBean> ListarConsulta(string Nro_Documento_cliente_web, DateTime FechaDesde, DateTime FechaHasta)
        {
            //System.Diagnostics.Debug.WriteLine("-------------------------------------------->" + Nro_Documento_cliente_web);

            try
            {
                List<ConsultaBean> listaConsulta = new List<ConsultaBean>();
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT * FROM Consulta";
                if (Nro_Documento_cliente_web == null)
                {
                    commandString = commandString + " WHERE estado = '1'";
                }
                else
                {
                    commandString = commandString + " WHERE id_cliente_web like '%" + Nro_Documento_cliente_web + "%'";
                    commandString = commandString + " AND estado = '1'";
                }
                //commandString = commandString + " WHERE id_cliente_web like '%" + Nro_Documento_cliente_web + "%'";
                //commandString = commandString + " AND estado = '1'";
                if (DateTime.Compare(FechaDesde, Convert.ToDateTime("01/01/0001")) != 0)
                {
                    commandString += " AND fecha BETWEEN substring('" + FechaDesde + "', 1, 10)"
                                  + " AND substring('" + FechaHasta + "', 1, 10)";
                }
                
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                //System.Diagnostics.Debug.WriteLine("--------------------------------------------inicio");

                while (dataReader.Read())
                {
                    ConsultaBean Consulta = new ConsultaBean();
                    Consulta.Id_Consulta = Convert.ToInt32(dataReader["Id_Consulta"]);
                    Consulta.Descripcion = Convert.ToString(dataReader["Descripcion"]);
                    //Consulta.Nro_Documento_cliente_web = Convert.ToString(dataReader["id_cliente_web"]);
                    // consulta.Nro_Documento = (string)dataReader["Nro_Documento"];
                    Consulta.estado = Convert.ToInt32(dataReader["estado"]);

                    listaConsulta.Add(Consulta);
                }
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao");
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao " + listaConsulta.Count());
                dataReader.Close();
                sqlCon.Close();
                return listaConsulta;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("-------------error----------------------fin consulta dao:" + e.ToString());
                return null;
            }

        }

        public List<BusquedaCliente> ListarConsultaCliente(string nombre, string apellido)
        {
            //System.Diagnostics.Debug.WriteLine("-------------------------------------------->" + Nro_Documento_cliente_web);

            try
            {
                List<BusquedaCliente> listaConsulta = new List<BusquedaCliente>();
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT b.id_cliente_web, a.nombres, a.apellidos FROM persona a, cliente_web b";
                commandString = commandString + " WHERE b.id_cliente_web = a.id_persona";
                commandString = commandString + " AND a.nombres like '%" + nombre + "%'";
                commandString = commandString + " AND a.apellidos like '%" + apellido + "%'";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                //System.Diagnostics.Debug.WriteLine("--------------------------------------------inicio");

                while (dataReader.Read())
                {
                    BusquedaCliente Consulta = new BusquedaCliente();
                    Consulta.id_cliente_web = Convert.ToString(dataReader["id_cliente_web"]);
                    Consulta.nombres = Convert.ToString(dataReader["nombres"]);
                    Consulta.apellidos = Convert.ToString(dataReader["apellidos"]);

                    listaConsulta.Add(Consulta);
                }
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao");
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao " + listaConsulta.Count());
                dataReader.Close();
                sqlCon.Close();
                return listaConsulta;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("-------------error----------------------fin consulta dao:" + e.ToString());
                return null;
            }

        }


        public List<BusquedaPropiedad> ListarConsultaPropiedad(string direccion)
        {
            //System.Diagnostics.Debug.WriteLine("-------------------------------------------->" + Nro_Documento_cliente_web);

            try
            {
                List<BusquedaPropiedad> listaConsulta = new List<BusquedaPropiedad>();
                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT * FROM propiedad_en_venta";
                commandString = commandString + " WHERE estado = '1' and direccion like '%" + direccion + "%'";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                //System.Diagnostics.Debug.WriteLine("--------------------------------------------inicio");

                while (dataReader.Read())
                {
                    BusquedaPropiedad Consulta = new BusquedaPropiedad();
                    Consulta.id_propiedad = Convert.ToString(dataReader["id_propiedad"]);
                    Consulta.direccion = Convert.ToString(dataReader["direccion"]);
                    Consulta.precio = Convert.ToString(dataReader["precioBase"]);
                    Consulta.area = Convert.ToString(dataReader["areaTotal"]);
                    listaConsulta.Add(Consulta);
                }
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao");
                //System.Diagnostics.Debug.WriteLine("--------------------------------------------fin consulta dao " + listaConsulta.Count());
                dataReader.Close();
                sqlCon.Close();
                return listaConsulta;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("-------------error----------------------fin consulta dao:" + e.ToString());
                return null;
            }

        }
        //public List<ConsultaBean> ListarConsultaMod(ConsultaBean consultaBean)
        //{
        //    List<ConsultaBean> listaConsulta = new List<ConsultaBean>();

        //    String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;

        //    SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
        //    sqlCon.Open();

        //    //string commandString0 = "UPDATE dbo.consulta SET id_cliente_web = '" + consultaBean.Nro_Documento_cliente_web + "',";
        //    commandString0 = commandString0 + "descripcion = '" + consultaBean.Descripcion + "',";
        //    commandString0 = commandString0 + "estado = '" + consultaBean.estado + "'";
        //    commandString0 = commandString0 + " WHERE Id_Consulta = '" + consultaBean.Id_Consulta + "'";
        //    SqlCommand sqlCmd0 = new SqlCommand(commandString0, sqlCon);
        //    sqlCmd0.ExecuteNonQuery();

        //    string commandString = "SELECT * FROM consulta";
        //    commandString = commandString + " WHERE Id_Consulta = " + consultaBean.Id_Consulta + "";
        //    SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
        //    SqlDataReader dataReader = sqlCmd.ExecuteReader();

        //    while (dataReader.Read())
        //    {
        //        ConsultaBean consulta2 = new ConsultaBean();
        //        //cliente. = (int)dataReader["idUsuario"];
        //        consulta2.Id_Consulta = (int)dataReader["Id_Consulta"];
        //        //consulta2.Nro_Documento_cliente_web = (int)dataReader["id_cliente_web"] + "";
        //        consulta2.Descripcion = (string)dataReader["descripcion"];
        //        consulta2.estado = (int)dataReader["estado"];


        //        listaConsulta.Add(consulta2);
        //    }
        //    dataReader.Close();
        //    sqlCon.Close();

        //    return listaConsulta;
        //}

        internal string correoVendedor(int p)
        {
            ConsultaBean resultado = new ConsultaBean();
            try
            {

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT id_vendedor FROM propiedad_en_venta WHERE id_propiedad=" + p;
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    resultado.Descripcion = Convert.ToString(dataReader["id_vendedor"]);
                }
                sqlCon.Close();
                System.Diagnostics.Debug.WriteLine("llegue aqui2.5 " + resultado.Descripcion);
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("llegue aqui2.5 " + e.ToString());
                return null;
            }
            try
            {

                String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
                SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
                sqlCon.Open();
                string commandString = "SELECT Correo FROM persona WHERE id_persona=" + resultado.Descripcion;
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    resultado.correo = Convert.ToString(dataReader["Correo"]);
                }
                sqlCon.Close();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("llegue aqui2.6 " + e.ToString());
                return null;
            }

            return resultado.correo;
        }
    }
}